/* 
 * Persistence4J - Simple library for data persistence using java
 * Copyright (c) 2010, Avdhesh yadav.
 * http://www.avdheshyadav.com
 * Contact: avdhesh.yadav@gmail.com
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 */

package com.avdheshyadav.p4j.jdbc.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import com.avdheshyadav.p4j.jdbc.PersistenceConfig;
import com.avdheshyadav.p4j.jdbc.dbms.DataSourceAttr;


/**
 * This class loads the sample data for the Apache Derby into the database
 * 
 * @author Avdhesh Yadav
 *
 */
public class DerbySampleDataLoader 
{
	private Connection connection;
	
	public DerbySampleDataLoader(String databaseName, String dbmsName, PersistenceConfig config) throws Exception
	{
		DataSourceAttr dataSourceAttr = new DataSourceAttr(DataSourceAttr.DEF_DERBY_EMBEDDED_DRIVER, DataSourceAttr.DEF_HOST, 0, config.getUserName(dbmsName), config.getPassword(dbmsName), config.getXaDataSourceClassName(dbmsName));
		Class.forName(dataSourceAttr.getDriverClassName());
		connection = DriverManager.getConnection(dataSourceAttr.getUrlString(databaseName), dataSourceAttr.getUserName(), dataSourceAttr.getPassword());
	}


	/**
	 * 
	 */
	public void createLibraryTables() throws SQLException
	{
		Statement s = connection.createStatement();
		System.out.println("insdie createLibraryTables");
		try {
			String book_drop = "DROP TABLE book";
			String book_issue_drop = "DROP TABLE book_issue";
			String book_inventory_drop = "DROP TABLE book_inventory";
			String student_drop = "DROP TABLE student";
			String author_drop = "DROP TABLE author";
			s.execute(book_drop);
			s.execute(book_issue_drop);
			s.execute(book_inventory_drop);
			s.execute(student_drop);
			s.execute(author_drop);
		} catch (SQLException e) {

		}

		// create book table
		String book = "CREATE TABLE book (isbn VARCHAR(45) NOT NULL, title VARCHAR(128) NOT NULL, authorid INT NOT NULL, PRIMARY KEY (isbn))";
		s.execute(book);

		// create book_issue table
		String book_issue = "CREATE TABLE book_issue (isbn VARCHAR(45) NOT NULL, rollno INT NOT NULL, date_of_issue TIMESTAMP NOT NULL,date_of_return TIMESTAMP DEFAULT NULL, PRIMARY KEY (isbn))";
		s.execute(book_issue);

		// create book_inventory table
		String book_inventory = "CREATE TABLE book_inventory (isbn VARCHAR(45) NOT NULL, no_available INT NOT NULL, totalbooks INT NOT NULL, PRIMARY KEY (isbn))";
		s.execute(book_inventory);


		// create student table
		String student = "CREATE TABLE student (rollno INT NOT NULL, name VARCHAR(45) NOT NULL, PRIMARY KEY (rollno))";
		s.execute(student);

		// create author table
		String author = "CREATE TABLE author (authorid INT NOT NULL, author_name VARCHAR(45) NOT NULL, PRIMARY KEY (authorid))";
		s.execute(author);

		s.close();
	}


	/**
	 * 
	 */
	public void insertRows() throws SQLException
	{
		Statement s = connection.createStatement();
		System.out.println("insdie insertRows");
		String book_sql = "INSERT INTO book VALUES ('81-203-0855-7','Computer System Architecture',4),('81-7366-270-3','Enterprise JavaBeans',3),('81-7366-381-5','Java RMI',1),('978-0-321-35668-0','Effective Java',2),('978-81-317-2446-0','Domain Driven Design',5)";
		String book_inventory_sql = "INSERT INTO library.book_inventory VALUES  ('81-203-0855-7',3,3),('81-7366-270-3',2,2),('81-7366-381-5',1,1),('978-0-321-35668-0',4,4),('978-81-317-2446-0',2,2)";
		String student_sql = "INSERT INTO student VALUES (401,'Ajay'),(402,'John'),(403,'Adrian Antal'),(404,'Avdhesh')";
		String author_sql = "INSERT INTO author VALUES (1,'william grosso'),(2,'joshua bloch'),(3,'Richard Monson'),(4,'Morris Mano'),(5,'Eric Evans'),(6,'Elliotte Rusty Harold')";

		s.execute(book_sql);
		s.execute(book_inventory_sql);
		s.execute(student_sql);
		s.execute(author_sql);
		s.close();
	}
}